
CREATE TABLE [dbo].[Media_MovieInfo](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[MovieId] [int] NULL,
	[HaveSeen] [bit] NULL,
	[HaveGot] [bit] NULL,
	[IsDownloading] [bit] NULL,
	[IsNewDownload] [bit] NULL,
	[CreatedDate] [datetime] NULL,
	[ModifiedDate] [datetime] NULL,
	[CreatedBy] [varchar](250) NULL,
	[ModifiedBy] [varchar](250) NULL,
 CONSTRAINT [PK_MovieInfo] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Media_MovieGenreList]    Script Date: 11/22/2011 22:06:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Media_MovieGenreList](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[MovieId] [int] NULL,
	[GenreId] [int] NULL,
 CONSTRAINT [PK_MovieGenreList] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Media_Movie]    Script Date: 11/22/2011 22:06:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Media_Movie](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[MovieTitle] [varchar](150) NULL,
	[Description] [varchar](max) NULL,
	[Director] [varchar](250) NULL,
	[ReleaseDate] [varchar](20) NULL,
	[Quality] [varchar](150) NULL,
	[Rating] [float] NULL,
	[Resolution] [varchar](1000) NULL,
	[Image] [varchar](max) NULL,
	[Favourite] [bit] NULL,
	[Size] [varchar](150) NULL,
	[CreatedDate] [datetime] NULL,
	[ModifiedDate] [datetime] NULL,
	[CreatedBy] [varchar](250) NULL,
	[ModifiedBy] [varchar](250) NULL,
	[FILENAME] [varchar](200) NULL,
 CONSTRAINT [PK_Movie] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Media_GenreList]    Script Date: 11/22/2011 22:06:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Media_GenreList](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[GenreTitle] [varchar](150) NULL,
 CONSTRAINT [PK_GenreList] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Main_TopNavMenu]    Script Date: 11/22/2011 22:06:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Main_TopNavMenu](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[MenuText] [varchar](150) NULL,
	[PageName] [varchar](150) NULL,
	[ControllerName] [varchar](150) NULL,
	[RoleId] [int] NULL,
	[IsActive] [bit] NULL,
 CONSTRAINT [PK_Movie_TopNavMenu] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Main_SaveLocation]    Script Date: 11/22/2011 22:06:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Main_SaveLocation](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Type] [varchar](250) NULL,
	[Location] [varchar](max) NULL,
	[IsActive] [bit] NULL,
 CONSTRAINT [PK_Media_SaveLocation] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Main_PageContent]    Script Date: 11/22/2011 22:06:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Main_PageContent](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[PageId] [int] NULL,
	[ContentName] [varchar](150) NULL,
	[PageContent] [varchar](max) NULL,
	[CreatedBy] [varchar](150) NULL,
	[CreatedDate] [datetime] NULL,
 CONSTRAINT [PK_Media_PageContent] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Main_Page]    Script Date: 11/22/2011 22:06:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Main_Page](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ControllerName] [varchar](150) NULL,
	[PageName] [varchar](150) NULL,
	[IsActive] [bit] NULL,
	[CreatedBy] [varchar](150) NULL,
	[CreatedDate] [datetime] NULL,
 CONSTRAINT [PK_Media_Page] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Main_FileTypes]    Script Date: 11/22/2011 22:06:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Main_FileTypes](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[FileType] [varchar](25) NULL,
	[IsActive] [bit] NULL,
 CONSTRAINT [PK_FileTypes] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Main_DownloadTabs]    Script Date: 11/22/2011 22:06:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Main_DownloadTabs](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](250) NULL,
	[Url] [varchar](500) NULL,
 CONSTRAINT [PK_Main_DownloadTabs] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Main_Directory]    Script Date: 11/22/2011 22:06:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Main_Directory](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](100) NULL,
	[Directory] [varchar](250) NULL,
	[IsActive] [bit] NULL,
	[CreatedDate] [datetime] NULL,
 CONSTRAINT [PK_Media_Directory] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Tv]    Script Date: 11/22/2011 22:06:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Tv](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Title] [varchar](200) NULL,
	[Description] [varchar](max) NULL,
	[Rating] [float] NULL,
	[Episodes] [int] NULL,
	[ReleaseDate] [varchar](50) NULL,
	[BannerImage] [varchar](max) NULL,
	[Status] [varchar](150) NULL,
 CONSTRAINT [PK_Tv] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Movie_NotFound]    Script Date: 11/22/2011 22:06:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Movie_NotFound](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[UserId] [int] NULL,
	[Movie] [varchar](150) NULL,
	[Date] [datetime] NULL,
 CONSTRAINT [PK_Movie_NotFound] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[TvShow_FileMatchTypes]    Script Date: 11/22/2011 22:06:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TvShow_FileMatchTypes](
	[Id] [int] NOT NULL,
	[Type] [varchar](350) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[TvShow_FileMatch]    Script Date: 11/22/2011 22:06:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TvShow_FileMatch](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Type] [varchar](350) NULL,
 CONSTRAINT [PK_TvShow_FileMatch] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[TvShow_Episodes]    Script Date: 11/22/2011 22:06:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TvShow_Episodes](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[TvId] [int] NULL,
	[TVDBId] [int] NULL,
	[SeasonId] [int] NULL,
	[EpisodeId] [int] NULL,
	[Title] [varchar](300) NULL,
	[Rating] [float] NULL,
	[Description] [varchar](max) NULL,
	[AirDate] [datetime] NULL,
	[FileLocation] [varchar](max) NULL,
	[Status] [varchar](20) NULL,
	[Quality] [varchar](50) NULL,
	[CreatedDate] [datetime] NULL,
	[CreatedBy] [varchar](150) NULL,
 CONSTRAINT [PK_TvShow_Episodes] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[TvShow_Directories]    Script Date: 11/22/2011 22:06:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TvShow_Directories](
	[Id] [int] NOT NULL,
	[TypeId] [varchar](150) NULL,
	[Location] [varchar](max) NULL,
 CONSTRAINT [PK_TvShow_Directories] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[TvShow_Watched]    Script Date: 11/22/2011 22:06:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TvShow_Watched](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[UserId] [int] NULL,
	[EpisodeId] [int] NULL,
 CONSTRAINT [PK_TvShow_Watched] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TvShow_Seasons]    Script Date: 11/22/2011 22:06:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TvShow_Seasons](
	[SeasonId] [int] IDENTITY(1,1) NOT NULL,
	[TvId] [int] NOT NULL,
	[Season] [varchar](50) NULL,
	[AirDate] [varchar](50) NULL,
	[Image] [varchar](max) NULL,
	[IsWatched] [bit] NULL,
	[Quality] [varchar](50) NULL,
	[CreatedBy] [varchar](50) NULL,
	[CreatedDate] [datetime] NULL,
 CONSTRAINT [PK_TvShow_Season] PRIMARY KEY CLUSTERED 
(
	[SeasonId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  StoredProcedure [dbo].[Main_Get_ExtensionTypes]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Main_Get_ExtensionTypes]
AS 

SELECT FileType 
FROM Main_FileTypes
WHERE IsActive = 1
GO
/****** Object:  StoredProcedure [dbo].[Main_Get_Directories]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Main_Get_Directories]
@Name VARCHAR(50)
AS 
IF (@Name = 'All')
BEGIN
	SELECT [Id], [Name],[Directory]
	FROM [dbo].[Main_Directory]
	WHERE IsActive = 1
END
ELSE
BEGIN
	SELECT [Id], [Name],[Directory]
	FROM [dbo].[Main_Directory]
	WHERE NAME = @Name
End
GO
/****** Object:  StoredProcedure [dbo].[TvShow_Insert_Seasons]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[TvShow_Insert_Seasons]
@SeriesName varchar(50), @SeasonNumber INT, @IsWatched BIT, @Image VARCHAR(MAX)
AS
Declare @TvId INT
SET @TvId = (Select Id from tv where title = @SeriesName)
IF NOT EXISTS(Select @Image From TvShow_Seasons WHERE TvId = @TvId AND Season = @SeasonNumber)
BEGIN
	Insert into TvShow_Seasons
		(TvId, Season, IsWatched, [Image], CreatedDate) 
	VALUES
		(@TvId, @SeasonNumber, @IsWatched, @Image, GetDate())
END
Select 'Inserted Successfully' as [message]
GO
/****** Object:  StoredProcedure [dbo].[TvShow_Insert_New]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[TvShow_Insert_New]
@ShowName VARCHAR(150), @Description VARCHAR(MAX), @Episodes INT, @Rating FLOAT, @FirstAired varchar(20),
@BannerImage VARCHAR(MAX), @Status VARCHAR(50)
AS
IF NOT EXISTS(SELECT Title FROM Tv WHERE Title = @ShowName)
BEGIN 
	INSERT INTO dbo.Tv
			( 
			  Title ,
			  [Description],
			  Rating ,
			  Episodes ,
			  ReleaseDate,
			  BannerImage,
			  [Status]
			)
	VALUES  ( 
			  @ShowName , -- Title - varchar(200)
			  @Description , -- Description - varchar(max)
			  @Rating , -- Rating - float
			  @Episodes , -- Episodes - int
			  @FirstAired,  -- ReleaseDate - varchar(50)
			  @BannerImage,
			  @Status
			)
END   

Select MAX(Id) AS Id From Tv
GO
/****** Object:  StoredProcedure [dbo].[TvShow_Insert_Episodes]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[TvShow_Insert_Episodes]
@TvId INT, @TVDBId INT, @SeasonNumber INT, @EpisodeNumber INT, @Name varchar(250), 
@Rating FLOAT, @Overview varchar(MAX), @AirDate DATETIME
AS


INSERT INTO TvShow_Episodes
	(TvId, TVDBId, SeasonId, EpisodeId, Title, Rating, [Description], AirDate, CreatedDate)
	
Select @TvId, @TVDBId, SeasonId, @EpisodeNumber, @Name, 
		@Rating, @Overview, @AirDate, GetDate()
from tvshow_seasons
Where TvId = @TvId And Season = @SeasonNumber
GO
/****** Object:  StoredProcedure [dbo].[TvShow_Get_ShowAndSeason]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[TvShow_Get_ShowAndSeason]
@SeasonId INT, @TvId INT
AS
--SET @TvId = 1182
--SET @SeasonId = 2347

select t.Title, s.Season  from Tv t
left join tvshow_seasons s on s.TvId = t.Id
Where TvId = @TvId and s.SeasonId = @SeasonId
GO
/****** Object:  StoredProcedure [dbo].[TvShow_Get_Seasons]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[TvShow_Get_Seasons]
@ShowName VARCHAR(250), @SeasonNumber INT, @User VARCHAR(250)
AS

Insert into TvShow_Season
	(
		TvId, Season, IsWatched, CreatedBy, CreatedDate	
	 )
	 
	 Select Id,  
	 @SeasonNumber,
	 0,
	 @User,
	 GetDate()
	 From Tv Where Title = @ShowName
GO
/****** Object:  StoredProcedure [dbo].[TvShow_Get_SeasonList]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[TvShow_Get_SeasonList]
@TvId INT
AS
select SeasonId, Season, IsWatched from tvshow_seasons
Where TvId = @TvId
Order By Season ASC
GO
/****** Object:  StoredProcedure [dbo].[TvShow_Get_SeasonImages]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[TvShow_Get_SeasonImages]
@Id INT
AS

Select [image]
From TvShow_Seasons
Where TvId = @Id
Order By Season ASC
GO
/****** Object:  StoredProcedure [dbo].[TvShow_Get_EpisodeBySeason]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[TvShow_Get_EpisodeBySeason]
@SeasonId INT, @TvId INT
AS

--Declare @SeasonId INT, @Season INT, @TvId INT
--SET @TvId = 1182
--SET @SeasonId = 2347

select e.SeasonId, EpisodeId as EpisodeNumber, e.Title, e.Rating, e.AirDate from tvshow_episodes e
left join tvshow_seasons s on e.SeasonId = s.SeasonId
Where e.TvId = @TvId and e.SeasonId = @SeasonId
GO
/****** Object:  StoredProcedure [dbo].[TvShow_Get_ComingSoon]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[TvShow_Get_ComingSoon]
AS


select top 30 E.Id AS EpisodeId, T.Title AS ShowName, Season, EpisodeId AS Episode,
E.Title AS EpisodeName, E.AirDate AS AirDate
from tvshow_episodes E
inner join Tvshow_Seasons S on S.SeasonId = E.SeasonId
inner join Tv T on T.Id = S.TvId
Where E.airdate + 2 >= GetDate() 
order by E.airdate asc


select * from tvshow_episodes
GO
/****** Object:  StoredProcedure [dbo].[TvShow_Check_FileTitle2]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[TvShow_Check_FileTitle2]
AS
Declare @EpisodeFile varchar(50), @SeriesId int, @SeasonId INT, @EpisodeId INT,
		@CurrentEpisode INT, @LastEpisode INT

SET @EpisodeFile = 'Breaking Bad - 03x05 - Pilot'
SET @SeriesId = (Select Id From Tv Where Title = 'Breaking Bad')
SET @SeasonId = (Select SeasonId From TvShow_Seasons Where TvId = @SeriesId AND Season = 3)

SET @CurrentEpisode = (SELECT MIN(Id) FROM TvShow_Episodes Where SeasonId = @SeasonId) -- Sets the first episode in the season
SET @LastEpisode = (SELECT MAX(Id) FROM TvShow_Episodes Where SeasonId = @SeasonId) -- Checks for the max episode in the season

--Loops through each episode in the season
WHILE (@CurrentEpisode <= @LastEpisode)
BEGIN
SET @EpisodeId = @CurrentEpisode -- sets the episode if to the current one its searching

	DECLARE @ShowName varchar(100), @SeasonNumber varchar(100), 
			@EpisodeNumber varchar(100), @TitleName varchar(100), 
			@CombinedName varchar(250)

	SET @ShowName = (Select Title From Tv Where Id = @SeriesId)
	SET @SeasonNumber = (Select Season From TvShow_Seasons Where SeasonId = @SeasonId)
	SET @EpisodeNumber = (Select EpisodeId From TvShow_Episodes Where Id = @EpisodeId)
	SET @TitleName = (Select Title From TvShow_Episodes Where Id = @EpisodeId)

	SET @CombinedName = '%' --Starts the beginning of the like--

	DECLARE @count INT, @Amount INT, @Items VARCHAR(1000)
	SET @count = 1
	SET @Amount = (select Count([Type]) From TvShow_FileMatchTypes)
	
	--Loops through and creates a string to match the episode filename
	WHILE (@count <= @Amount)
	BEGIN
		Set @Items = (select Type From TvShow_FileMatchTypes Where Id = @Count)
		SET @count = (@count + 1)

		DECLARE @Item VARCHAR(50)
		DECLARE @Pos INT
		DECLARE @Loop BIT
		SELECT @Loop = CASE WHEN LEN(@Items) > 0 THEN 1 ELSE 0 END
		WHILE (SELECT @Loop) = 1
		BEGIN
		SELECT @Pos = CHARINDEX('+', @Items, 1)
		IF @Pos > 0
			BEGIN
				SELECT @Item = SUBSTRING(@Items, 1, @Pos - 1)
				SELECT @Items = SUBSTRING(@Items, @Pos + 1, LEN(@Items) - @Pos)
				IF (SELECT @Item) = 'ShowTitle '
				Begin
					SET @CombinedName = (@CombinedName + '' + @ShowName)
				End
				IF (SELECT @Item) = ' dash '
				Begin
					SET @CombinedName = (@CombinedName + ' ' + '-')
				End
				IF (SELECT @Item) = ' dashSpace '
				Begin
					SET @CombinedName = (@CombinedName + ' ' + '- ')
				End
				IF (SELECT @Item) = ' SeasonLetter '
				Begin
					SET @CombinedName = (@CombinedName + ' ' + 'S')
				End
				IF (SELECT @Item) = ' SeasonLetterNoSpace '
				Begin
					SET @CombinedName = (@CombinedName + 'S')
				End
				IF (SELECT @Item) = ' EpisodeLetter '
				Begin
					SET @CombinedName = (@CombinedName + ' ' + 'E')
				End
				IF (SELECT @Item) = ' EpisodeLetterNoSpace '
				Begin
					SET @CombinedName = (@CombinedName + 'E')
				End
				IF (SELECT @Item) = ' xNoSpace '
				Begin
					SET @CombinedName = (@CombinedName + 'x')
				End
				IF (SELECT @Item) = ' Season '
				Begin
					SET @CombinedName = (@CombinedName + ' ' + @SeasonNumber)
				End
				IF (SELECT @Item) = ' SeasonNoSpace '
				Begin
					SET @CombinedName = (@CombinedName + @SeasonNumber)
				End
				IF (SELECT @Item) = ' dot '
				Begin
					SET @CombinedName = (@CombinedName + ' ' + '.')
				End
				IF (SELECT @Item) = ' dotNoSpace '
				Begin
					SET @CombinedName = (@CombinedName + '.')
				End
				IF (@EpisodeNumber < 10)
				BEGIN
					IF (SELECT @Item) = ' EpisodeZero '
					Begin
						SET @CombinedName = (@CombinedName + '0')
					End
				END
				IF (@SeasonNumber < 10)
				BEGIN
					IF (SELECT @Item) = ' SeasonZero '
					Begin
						SET @CombinedName = (@CombinedName + '0')
					End
				END
				IF (SELECT @Item) = ' Episode '
				Begin
					SET @CombinedName = (@CombinedName + @EpisodeNumber)
				End
				IF (SELECT @Item) = ' EpisodeTitle '
				Begin
					SET @CombinedName = (@CombinedName + ' ' + @TitleName)
				End
				IF (SELECT @Item) = ' EpisodeTitleNoSpace '
				Begin
					SET @CombinedName = (@CombinedName + @TitleName)
				End
			END
			ELSE
			BEGIN
				SELECT @Item = @Items
				SELECT @Loop = 0
			END
		END
		SET @CombinedName = (@CombinedName + '%')

		IF (@EpisodeFile) LIKE @CombinedName
		Begin
			SELECT EpisodeId From Tvshow_Episodes Where Id = @CurrentEpisode
			SELECT 'TRUE'
		END
		SET @CombinedName = '%'
	END
	SET @CurrentEpisode = @CurrentEpisode + 1
END
GO
/****** Object:  StoredProcedure [dbo].[TvShow_Check_FileTitle]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[TvShow_Check_FileTitle]
AS
Declare @EpisodeFile varchar(50), @SeriesId int, @SeasonId INT, @EpisodeId INT

SET @EpisodeFile = 'bones s6 e19'
SET @SeriesId = 1189
SET @SeasonId = 2360
SET @EpisodeId = 19065


Declare @ShowName varchar(100), @SeasonNumber varchar(100), 
@EpisodeNumber varchar(100), @TitleName varchar(100), @CombinedName varchar(250)

SET @ShowName = (Select Title From Tv Where Id = @SeriesId)
SET @SeasonNumber = (Select Season From TvShow_Seasons Where SeasonId = @SeasonId)
SET @EpisodeNumber = (Select EpisodeId From TvShow_Episodes Where Id = @EpisodeId)
SET @TitleName = (Select Title From TvShow_Episodes Where Id = @EpisodeId)

SET @CombinedName = '%'

DECLARE @count INT, @Amount INT, @Items VARCHAR(1000)
SET @count = 1
SET @Amount = (select Count(Type) From TvShow_FileMatchTypes)
WHILE (@count <= @Amount)
BEGIN
	Set @Items = (select Type From TvShow_FileMatchTypes Where Id = @Count)
    SET @count = (@count + 1)

	DECLARE @Item VARCHAR(50)
	DECLARE @Pos INT
	DECLARE @Loop BIT
	SELECT @Loop = CASE WHEN LEN(@Items) > 0 THEN 1 ELSE 0 END
	WHILE (SELECT @Loop) = 1
	BEGIN
	SELECT @Pos = CHARINDEX('+', @Items, 1)
	IF @Pos > 0
		BEGIN
			SELECT @Item = SUBSTRING(@Items, 1, @Pos - 1)
			SELECT @Items = SUBSTRING(@Items, @Pos + 1, LEN(@Items) - @Pos)
			IF (SELECT @Item) = 'ShowTitle '
			Begin
				SET @CombinedName = (@CombinedName + '' + @ShowName)
			End
			IF (SELECT @Item) = ' dash '
			Begin
				SET @CombinedName = (@CombinedName + ' ' + '-')
			End
			IF (SELECT @Item) = ' SeasonLetter '
			Begin
				SET @CombinedName = (@CombinedName + ' ' + 'S')
			End
			IF (SELECT @Item) = ' SeasonLetterNoSpace '
			Begin
				SET @CombinedName = (@CombinedName + 'S')
			End
			IF (SELECT @Item) = ' EpisodeLetter '
			Begin
				SET @CombinedName = (@CombinedName + ' ' + 'E')
			End
			IF (SELECT @Item) = ' EpisodeLetterNoSpace '
			Begin
				SET @CombinedName = (@CombinedName + 'E')
			End
			IF (SELECT @Item) = ' xNoSpace '
			Begin
				SET @CombinedName = (@CombinedName + 'x')
			End
			IF (SELECT @Item) = ' Season '
			Begin
				SET @CombinedName = (@CombinedName + ' ' + @SeasonNumber)
			End
			IF (SELECT @Item) = ' SeasonNoSpace '
			Begin
				SET @CombinedName = (@CombinedName + @SeasonNumber)
			End
			IF (SELECT @Item) = ' dot '
			Begin
				SET @CombinedName = (@CombinedName + ' ' + '.')
			End
			IF (SELECT @Item) = ' dotNoSpace '
			Begin
				SET @CombinedName = (@CombinedName + '.')
			End
			IF (@EpisodeNumber < 10)
			BEGIN
				IF (SELECT @Item) = ' EpisodeZero '
				Begin
					SET @CombinedName = (@CombinedName + '0')
				End
			END
			IF (@SeasonNumber < 10)
			BEGIN
				IF (SELECT @Item) = ' SeasonZero '
				Begin
					SET @CombinedName = (@CombinedName + '0')
				End
			END
			IF (SELECT @Item) = ' Episode '
			Begin
				SET @CombinedName = (@CombinedName + @EpisodeNumber)
			End
			IF (SELECT @Item) = ' EpisodeTitle '
			Begin
				SET @CombinedName = (@CombinedName + ' ' + @TitleName)
			End
			IF (SELECT @Item) = ' EpisodeTitleNoSpace '
			Begin
				SET @CombinedName = (@CombinedName + @TitleName)
			End
		END
		ELSE
		BEGIN
			SELECT @Item = @Items
			SELECT @Loop = 0
		END
	END
	SET @CombinedName = (@CombinedName + '%')

	IF (@EpisodeFile) LIKE @CombinedName
	Begin
		SELECT 'TRUE'
		BREAK
	END
	SET @CombinedName = '%'
END
GO
/****** Object:  StoredProcedure [dbo].[TvSeries_Get_TvSeriesInfo]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[TvSeries_Get_TvSeriesInfo]
@Id INT
AS

--Declare @Id INT
--SET @Id = 1281

Select T.Id, T.Title, T.[Description], 

(Select Count(EpisodeId) From TvShow_Episodes E
join TvShow_Seasons S ON S.SeasonId = E.SeasonId
Where E.TvId = @Id AND Season <> 0) AS Episodes, 
		
(Select Count(SeasonId) From TvShow_Seasons 
Where TvId = @Id AND Season <> 0) AS Seasons,

T.Rating, T.ReleaseDate, T.[Status], T.BannerImage  
		
FROM tvshow_episodes E
inner join Tv T on T.Id = E.TvId
WHERE SeasonId <> 0 and E.rating <> -99 AND T.Id = @Id
Group By T.Id, T.Title, T.Rating, T.BannerImage, T.[Description], T.ReleaseDate, T.[Status]
GO
/****** Object:  StoredProcedure [dbo].[Tv_Insert_NewTvShows]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Tv_Insert_NewTvShows]
@Title VARCHAR(150)
AS

IF NOT EXISTS(SELECT Title FROM Tv WHERE Title = @Title)
BEGIN 
	INSERT INTO dbo.Tv
			( Title 
			)
	VALUES  ( @Title
			)
END
GO
/****** Object:  StoredProcedure [dbo].[Tv_Get_TvShows]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Tv_Get_TvShows]
AS

Select T.Id, T.Title, Count(EpisodeId) AS Episodes, T.Rating, T.BannerImage  
FROM tvshow_episodes E
inner join Tv T on T.Id = E.TvId
WHERE SeasonId <> 0 and E.rating <> -99
Group By T.Id, T.Title, T.Rating, T.BannerImage
Order by T.Title ASC
GO
/****** Object:  StoredProcedure [dbo].[Movie_Insert_NotFound]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Movie_Insert_NotFound]
@Title VARCHAR(150)
AS

INSERT INTO Movie_NotFound (UserId, Movie, [Date])
VALUES (1, @Title, GETDATE())
GO
/****** Object:  StoredProcedure [dbo].[Movie_Insert_NewMovies]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Movie_Insert_NewMovies]
@FileName VARCHAR(200), @Title VARCHAR(150), @Plot VARCHAR(MAX), @Directors VARCHAR(150),
@Year VARCHAR(10), @Rating FLOAT, @SaveLocation VARCHAR(MAX), @Type VARCHAR(50)
AS

IF NOT EXISTS(SELECT MovieTitle FROM dbo.Media_Movie WHERE MovieTitle = @Title)
	BEGIN
	INSERT INTO dbo.Media_Movie
				(	
					[FileName], 
					MovieTitle, 
					[Description], 
					Director, 
					[ReleaseDate], 
					Rating, 
					[Image]
				)
		VALUES	( 
					@FileName,
					@Title, 
					@Plot, 
					@Directors, 
					@Year, 
					@Rating, 
					@SaveLocation
				)
END

SELECT 'Add Successfully'
GO
/****** Object:  StoredProcedure [dbo].[Movie_Get_List]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[Movie_Get_List]
AS

SELECT [Id],[MovieTitle],[ReleaseDate], 'Success' as [message], '1' as [check]
FROM [Media_Movie]
GO
/****** Object:  StoredProcedure [dbo].[Movie_Get_FileName]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Movie_Get_FileName]
@Id INT 
AS

SELECT [FileName] FROM dbo.Media_Movie
WHERE Id = @Id
GO
/****** Object:  StoredProcedure [dbo].[Movie_Get_CompleteList]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Movie_Get_CompleteList]
AS
SELECT Id, MovieTitle
  FROM [dbo].[Media_Movie]
  ORDER BY MovieTitle ASC
GO
/****** Object:  StoredProcedure [dbo].[Movie_Get_ById]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[Movie_Get_ById] 
@Id INT
as

SELECT *
From Media_Movie
Where Id = @Id
GO
/****** Object:  StoredProcedure [dbo].[Downloads_Get_Tabs]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Downloads_Get_Tabs]
AS
SELECT * FROM Main_DownloadTabs
GO
/****** Object:  StoredProcedure [dbo].[Admin_Update_Directories]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[Admin_Update_Directories]
@Id INT, @Name VARCHAR(250), @Directory VARCHAR(MAX)
AS

Update Main_Directory
SET Directory = @Directory
WHERE Name = @Name

Select 'Updated Successfully' as [Message], 1 as [Check]
GO
/****** Object:  StoredProcedure [dbo].[Admin_Get_TvInfo]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  PROCEDURE [dbo].[Admin_Get_TvInfo]
AS

SELECT COUNT(Title) AS TotalTvShows
FROM dbo.Tv
GO
/****** Object:  StoredProcedure [dbo].[Admin_Get_MovieInfo]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Admin_Get_MovieInfo]
AS

SELECT COUNT(MovieTitle) AS TotalMovies, '\\robsshare\mac\media\movies' AS MovieDir
FROM dbo.Media_Movie
GO
/****** Object:  StoredProcedure [dbo].[Admin_Get_Menus]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Admin_Get_Menus]
AS
SELECT * FROM Main_TopNavMenu
GO
/****** Object:  StoredProcedure [dbo].[Admin_Get_Directories]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Admin_Get_Directories]
AS 

SELECT [Id], [Name],[Directory]
FROM [Main_Directory]
WHERE isActive = 1
GO
/****** Object:  StoredProcedure [dbo].[Main_Insert_ImageLocation]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Main_Insert_ImageLocation]
@Id INT, @Type VARCHAR(50), @Movie VARCHAR(250), @Extension VARCHAR(8)
AS

IF (@Type = 'Movie')
BEGIN
	UPDATE dbo.Media_Movie
	SET [Image] = ('../../Content/MediaImages/Movie/' + @Movie + @Extension)
	WHERE Id = @Id
END

SELECT * FROM dbo.Main_Movie
WHERE MovieTitle LIKE '%17 again%'
GO
/****** Object:  StoredProcedure [dbo].[Main_Get_SaveLocation]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Main_Get_SaveLocation]
@Type VARCHAR(50)
AS

SELECT [Location]
FROM [dbo].[Main_SaveLocation]
WHERE IsActive = 1 AND [Type] = @Type
GO
/****** Object:  StoredProcedure [dbo].[Main_Get_PageContent]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Main_Get_PageContent]
@ControllerName VARCHAR(150), @PageName VARCHAR(150), @ContentName VARCHAR(150)
AS

SELECT PageContent 
FROM dbo.Main_PageContent
INNER JOIN dbo.Main_Page ON dbo.Main_PageContent.PageId = dbo.Main_Page.Id
WHERE ControllerName = @ControllerName AND PageName = @PageName AND ContentName = @ContentName
GO
/****** Object:  StoredProcedure [dbo].[Main_Get_MenuByRole]    Script Date: 11/22/2011 22:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Main_Get_MenuByRole]
@UserName VARCHAR(150)
AS
DECLARE @RoleId INT 

IF (SELECT RoleName FROM Media_Accounts.dbo.aspnet_Roles
	INNER JOIN Media_Accounts.dbo.aspnet_UsersInRoles ON Media_Accounts.dbo.aspnet_Roles.RoleId = Media_Accounts.dbo.aspnet_UsersInRoles.RoleId
	INNER JOIN Media_Accounts.dbo.aspnet_Users ON Media_Accounts.dbo.aspnet_UsersInRoles.UserId = Media_Accounts.dbo.aspnet_Users.UserId
	WHERE UserName = @UserName) = 'Administrator'
BEGIN
	SET @RoleId = 3
END 

ELSE IF (SELECT RoleName FROM Media_Accounts.dbo.aspnet_Roles
		INNER JOIN Media_Accounts.dbo.aspnet_UsersInRoles ON Media_Accounts.dbo.aspnet_Roles.RoleId = Media_Accounts.dbo.aspnet_UsersInRoles.RoleId
		INNER JOIN Media_Accounts.dbo.aspnet_Users ON Media_Accounts.dbo.aspnet_UsersInRoles.UserId = Media_Accounts.dbo.aspnet_Users.UserId
		WHERE UserName = @UserName) = 'Power User'
BEGIN
	SET @RoleId = 2
END 

ELSE
BEGIN
	SET @RoleId = 1
END 														

SELECT MenuText, PageName, ControllerName 
FROM Main_TopNavMenu
WHERE MenuText = 'Home' AND isActive = 1 AND RoleId <= @RoleId
UNION ALL 
SELECT MenuText, PageName, ControllerName 
FROM Main_TopNavMenu
WHERE MenuText NOT IN ('Home', 'About') AND isActive = 1 AND RoleId <= @RoleId
UNION ALL 
SELECT MenuText, PageName, ControllerName 
FROM Main_TopNavMenu
WHERE MenuText = 'About' AND isActive = 1 AND RoleId <= @RoleId
GO
